package cn.com.zsc.hr;

import java.util.Hashtable;
import java.util.Vector;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;
import cn.com.zsc.util.ConstValue;

import com.jarp.db.DAO;

/**
 * 职工管理报表
 * @author 杨景
 *
 */
public class EmployeeMessages {
	
	/**
	 * 获取职工信息
	 * @param start
	 * @param limit
	 * @param isFormal
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmployee() {
		String sql = "SELECT" +
						" B.CL_NAME AS SHOW_SEX," +
						" C.CL_NAME AS SHOW_EPMODE," +
						" D.CL_NAME AS SHOW_STARLEVEL," +
						" E.CL_NAME AS SHOW_PVISAGE," +
						" F.CL_NAME AS SHOW_DEPT," +
						" G.CL_NAME AS SHOW_CONTRACT," +
						" H.CL_NAME AS SHOW_EDUCATION," + 
						" I.CL_NAME AS SHOW_CCARD," +
						" J.CL_NAME AS SHOW_ELEVEL," +
						" K.CL_NAME AS SHOW_MLEVEL," +
						" L.CL_NAME AS SHOW_TERM," + 
						" M.CL_NAME AS SHOW_POST, " +
						" N.CL_NAME AS SHOW_DUTIES, " +
						" O.CL_NAME AS SHOW_LEFTJOB, " +
						" P.CL_NAME AS SHOW_INSURANCE, " +
						" A.* " +
					" FROM HR_EMPLOYEE A " +
							" LEFT JOIN CM_COMMLIST B ON A.SEX=B.CL_CODE " +
							" LEFT JOIN CM_COMMLIST C ON A.EPMODE=C.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST D ON A.STARLEVEL=D.CL_CODE " +
							" LEFT JOIN CM_COMMLIST E ON A.PVISAGE=E.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST F ON A.DEPT=F.CL_CODE " +
							" LEFT JOIN CM_COMMLIST G ON A.CONTRACT=G.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST H ON A.EDUCATION=H.CL_CODE " +
							" LEFT JOIN CM_COMMLIST I ON A.CCARD=I.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST J ON A.ELEVEL=J.CL_CODE " +
							" LEFT JOIN CM_COMMLIST K ON A.MLEVEL=K.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST L ON A.TERM=L.CL_CODE " +
							" LEFT JOIN CM_COMMLIST M ON A.POST=M.CL_CODE " +
							" LEFT JOIN CM_COMMLIST N ON A.DUTIES=N.CL_CODE " +
							" LEFT JOIN CM_COMMLIST O ON A.LEFTJOB=O.CL_CODE " +
							" LEFT JOIN CM_COMMLIST P ON A.INSURANCE=P.CL_CODE " +
					"ORDER by EID desc";
		
        DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;

		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				for (int i=0; i<v.size(); i++) {
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("BIRTHDAY"))) {
						v.get(i).put("BIRTHDAY", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("ARRDATE"))) {
						v.get(i).put("ARRDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("SIGNDATE"))) {
						v.get(i).put("SIGNDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("LIMITDATE"))) {
						v.get(i).put("LIMITDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("INDATE"))) {
						v.get(i).put("INDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("GRADUATION"))) {
						v.get(i).put("GRADUATION", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("AGREEMENT"))) {
						v.get(i).put("AGREEMENT", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("ENDDATE"))) {
						v.get(i).put("ENDDATE", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("TEACHER"))) {
						v.get(i).put("TEACHER", "");
					}
					
					if(ConstValue.DEFAULT_DATE.equals(v.get(i).get("INSURANCE"))) {
						v.get(i).put("INSURANCE", "");
					}
				}
				
				JSONArray json = JSONArray.fromObject(v);				
				
				resultStr = json.toString();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultStr;
	}
	
	/**
	 * 获取对应条数的职工信息
	 * @param start
	 * @param limit
	 * @param isFormal
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public String getEmployee(String jso) {
		JSONObject jsobject = JSONObject.fromObject(jso);
				
		String eName = jsobject.getString("eName");
		String workID = jsobject.getString("workID");
		String starLevel = jsobject.getString("starLevel");
		String term = jsobject.getString("term");
		String contract = jsobject.getString("contract");
		String leftJob = jsobject.getString("leftJob");
		String startDate = jsobject.getString("startDate");
		String endDate = jsobject.getString("endDate");
		String isFormal = jsobject.getString("isFormal");
		
		String sql = "SELECT" +
						" B.CL_NAME AS SHOW_SEX," +
						" C.CL_NAME AS SHOW_EPMODE," +
						" D.CL_NAME AS SHOW_STARLEVEL," +
						" E.CL_NAME AS SHOW_PVISAGE," +
						" F.CL_NAME AS SHOW_DEPT," +
						" G.CL_NAME AS SHOW_CONTRACT," +
						" H.CL_NAME AS SHOW_EDUCATION," + 
						" I.CL_NAME AS SHOW_CCARD," +
						" J.CL_NAME AS SHOW_ELEVEL," +
						" K.CL_NAME AS SHOW_MLEVEL," +
						" L.CL_NAME AS SHOW_TERM," + 
						" M.CL_NAME AS SHOW_POST, " +
						" N.CL_NAME AS SHOW_DUTIES, " +
						" O.CL_NAME AS SHOW_LEFTJOB, " +
						" A.* " +
					" FROM HR_EMPLOYEE A " +
							" LEFT JOIN CM_COMMLIST B ON A.SEX=B.CL_CODE " +
							" LEFT JOIN CM_COMMLIST C ON A.EPMODE=C.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST D ON A.STARLEVEL=D.CL_CODE " +
							" LEFT JOIN CM_COMMLIST E ON A.PVISAGE=E.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST F ON A.DEPT=F.CL_CODE " +
							" LEFT JOIN CM_COMMLIST G ON A.CONTRACT=G.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST H ON A.EDUCATION=H.CL_CODE " +
							" LEFT JOIN CM_COMMLIST I ON A.CCARD=I.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST J ON A.ELEVEL=J.CL_CODE " +
							" LEFT JOIN CM_COMMLIST K ON A.MLEVEL=K.CL_CODE " + 
							" LEFT JOIN CM_COMMLIST L ON A.TERM=L.CL_CODE " +
							" LEFT JOIN CM_COMMLIST M ON A.POST=M.CL_CODE " +
							" LEFT JOIN CM_COMMLIST N ON A.DUTIES=N.CL_CODE " +
							" LEFT JOIN CM_COMMLIST O ON A.LEFTJOB=O.CL_CODE where 1=1 ";
		
		if (!"".equals(eName)) {
            sql += " and A.ENAME like '%" + eName + "%'";
		}
		
		if (!"".equals(workID)) {
            sql += " and A.WORKID='" + workID + "'";
		}
		
		if (!"".equals(starLevel)) {
            sql += " and A.STARLEVEL=" + starLevel;
		}
		
		if (!"".equals(term)) {
            sql += " and A.TERM=" + term;
		}
		
		if (!"".equals(contract)) {
            sql += " and A.CONTRACT=" + contract;
		}
		
		if (!"".equals(leftJob)) {
            sql += " and A.LEFTJOB=" + leftJob;
		}
		
		if (!"".equals(startDate)) {
            sql += " and A.SIGNDATE>='" + startDate + "'";
		}
		
		if (!"".equals(endDate)) {
            sql += " and A.SIGNDATE<='" + endDate + "'";
		}
		
		if (!"".equals(isFormal)) {
            sql += " and A.ISFORMAL=" + isFormal;
		}
		
		sql += " order by A.EID desc";

		DAO dao = new DAO();
		
		String resultStr = ConstValue.EMPTY_RECORD_NOT_PAGEING;

		try {
			Vector<Hashtable<String, String>> v = dao.getDataSet(ConstValue.DBNAME, sql);

			if (v != null && v.size() > 0) {
				JSONArray json = JSONArray.fromObject(v);				
				
				resultStr = json.toString();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return resultStr;
	}
}
